To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96
Chapter 31 Introducing SQL Development Tools
When building client applications, it is sometimes useful to employ Oracle or third-party tools to enhance the efficiency of these applications. These tools are available for many areas and can help with many different aspects of your application and database design.
Tools fall into several different categories:
- Database design tools. This type of tool helps in the design of the database itself. By feeding the tool an outline of the data to be stored in the databaseas well as the data relationshipsthis type of tool designs the tablespaces, tables, clusters, and indexes.
- Application builders. This type of tool is used to actually build the client application. The tools typically have an emphasis on the Graphical User Interface (GUI).
- Analysis tools. These tools examine or trace your SQL statements, try to determine inefficiencies and bottlenecks, and recommend ways to correct those problems.
The following sections examine a few of these different types of tools and describe how they can help you optimize your database.
Database Design Tools
Designing a database can be a difficult task, even for the most seasoned Oracle users. When a database is made up of ten or more tables, each with relationships to other tables, the task can be even more difficult. Along with building the tablespaces, tables, clusters, and indexes is the complexity of deciding how to arrange the physical data files to most efficiently distribute the I/Os.
By taking advantage of some of the database design tools available on the market today, you can reduce some of this complexity. Database design tools allow you to input information about the data that must be stored in the database and the relationships between that data; they produce a recommended database layout you can take and modify. No matter how good the design tool, you must still make the final decisions and modifications.
These tools have come a long way in the last few years and have seen an incredible amount of improvement. Even so, you must be the final judge of the design of your database. Take these tools, use them, and modify the output to suit your needs. Database design tools are available from several vendors including Oracle. Oracle includes this type of design tool as part of its Designer/2000 package. Other tools include S-Designor from SDP Technologies, ERwin/ERX from Logic Works, and SQL Coder from Platinum Technologies.
Oracle Designer/2000
The Oracle Designer/2000 is a tool developed by Oracle to assist the corporate customer in designing and implementing an information management system. Designer/2000 is based on rapid development of information systems that are business driven. Designer/2000 is intended to help businesses develop the information system they need to re-engineer the way they do business.
Designer/2000 is repository based and is designed to allow users to share work; a team of people can use Designed/2000 to effectively and efficiently share the design effort. Team-based design tools make it easier to share work and ideas and reduce the dependency on one particular team member.
Designer/2000 is not a tool; it is a set of tools that interface and work together for the same goal. The Designer/2000 suite of tools includes the following products:
- Process Modeller. This tool is used to assist in modeling your system based on your business process.
- Systems Modeller. This tool is used to assist in modeling your system based on the information available in your system.
- Systems Designer. This is the tool that supports the design of the information system.
- Generators. These utilities take the data models you have created with the other Designer/2000 tools and create client-side and server-side code to help in the creation of data definitions and applications.
The following sections look briefly at each of these design tools.
Process Modeller
The Process Modeller focuses on the essentials of the process for which the information system is designed. These essentials include the primary steps of the business process, how these processes are linked, and who is responsible for these steps. These elements are the fundamentals of the business process, including how the organization operates. If the business process is not correctly modeled, it is impossible to effectively design the information system to support the business process.
Systems Modeller
Another way to base your system design is on the information itself rather than on the process. Either of these methods is effective. The Systems Modeller takes the information used in the business process as the basis for the systems design.
The Systems Modeller utility includes the following components:
- Entity Relationship Diagrammer, used to construct entity relationship models.
- Function Hierarchy Diagrammer, used for the construction of functional hierarchies.
- Dataflow Diagrammer, used for the construction and testing of dataflow models.
These components are used to diagram and relate the information used by various organizations within your corporation.
Systems Designer
The Systems Designer is the component that actually takes the process and relationship information and supports you in your system-design tasks. The output of the System Designer tools includes such things as these:
- Data definitions
- Business rules
- Procedure definitions
- Transaction definitions
- Data usage definitions
- Workflow definitions
- Queries and reports
The System Designer allows you to input and output additional information concerning the structure of your system and process rules. The System Designer includes tools such as a Data Diagrammer and a Structure Diagrammer that allow the design of individual modules. Information from your data models is used to verify that relationships and process rules are followed. The System Designer includes many design wizards to assist in the creation of these modules.
|